﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Yunlib.Common;
using Yunlib.Entity;
using YunLib.Common;

namespace Yunlib.DAL
{
    public class BorrowForAcess
    {
        /// <summary>
        /// 获取书名
        /// </summary>
        /// <returns></returns>
        public DataTable GetBookToDataTable()
        {
            string sqlText = "SELECT BName,DoorID,DoorCode,DoorNUM FROM LocalBook WHERE BName IS NOT NULL AND BName <> '' ORDER BY DoorNUM";
            DataSet ds = AcessHelper.ExecuteDataSet(sqlText);
            DataTable dt = ds.Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取一个书柜上一行的所有书籍
        /// </summary>
        /// <param name="doorId"></param>
        /// <returns></returns>
        public DataTable GetBookByDoorID(int doorId)
        {
            string sqlText = "select * from LocalBook where DoorID = {0} and BName is not null order by ID".FormatWith(doorId);

            DataSet ds = AcessHelper.ExecuteDataSet(sqlText);
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public IList<LocalBooks> GetBookByDoorNum(int doorNum)
        {
            string sqlText = "select * from LocalBook where DoorNum = {0}".FormatWith(doorNum);
            DataSet ds = AcessHelper.ExecuteDataSet(sqlText);
            DataTable dt = ds.Tables[0];
            IList<LocalBooks> list = DataTable2List<LocalBooks>.ConvertToModel(dt);
            return list;
        }

        /// <summary>
        /// 获取空书柜的集合
        /// </summary>
        /// <returns></returns>
        public IList<LocalBooks> GetNullBookCase()
        {
            string sqlText = "select * from Localbook where BarCode IS NULL or BarCode = '' ORDER BY ID";
            DataSet ds = AcessHelper.ExecuteDataSet(sqlText);
            DataTable dt = ds.Tables[0];
            IList<LocalBooks> list = DataTable2List<LocalBooks>.ConvertToModel(dt);
            return list;
        }

        public bool FillBookToAcess(LocalBooks lb)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" Update Localbook Set BarCode='{0}'".FormatWith(lb.BarCode));
            sqlStr.AppendLine(@"                     ,BName='{0}'".FormatWith(lb.BName));
            sqlStr.AppendLine(@"                     ,Writer='{0}'".FormatWith(lb.Writer));
            sqlStr.AppendLine(@"                     ,Classify='{0}'".FormatWith(lb.Classify));
            sqlStr.AppendLine(@"                     ,Press='{0}'".FormatWith(lb.Press));
            sqlStr.AppendLine(@"                     ,PublicationDate='{0}'".FormatWith(lb.PublicationDate));
            sqlStr.AppendLine(@"                     ,Introduction='{0}'".FormatWith(lb.Introduction));
            sqlStr.AppendLine(@" WHERE DoorID={0} AND DoorCode={1}".FormatWith(lb.DoorID, lb.DoorCode));

            return AcessHelper.ExecuteNonQuery(sqlStr.ToString());

        }

        public bool DeleteBookByID(LocalBooks lb) {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.AppendLine(@" Update Localbook Set BarCode='{0}'".FormatWith(lb.BarCode));
            sqlStr.AppendLine(@"                     ,BName='{0}'".FormatWith(lb.BName));
            sqlStr.AppendLine(@"                     ,Writer='{0}'".FormatWith(lb.Writer));
            sqlStr.AppendLine(@"                     ,Classify='{0}'".FormatWith(lb.Classify));
            sqlStr.AppendLine(@"                     ,Press='{0}'".FormatWith(lb.Press));
            sqlStr.AppendLine(@"                     ,PublicationDate='{0}'".FormatWith(lb.PublicationDate));
            sqlStr.AppendLine(@" WHERE ID={0}".FormatWith(lb.ID));

            return AcessHelper.ExecuteNonQuery(sqlStr.ToString());
        }


        public LocalBooks GetBookByBarCode(string barCode)
        {
            string sqlText = "select * from Localbook where BarCode = '{0}'".FormatWith(barCode);
            DataSet ds = AcessHelper.ExecuteDataSet(sqlText);
            DataTable dt = ds.Tables[0];
            IList<LocalBooks> list = DataTable2List<LocalBooks>.ConvertToModel(dt);
            if (list.Count > 0 && list != null)
            {
                return list[0];
            }
            else
            {
                return null;
            }
        }

        public int GetTotalCount()
        {
            string sqlText = "SELECT * FROM Localbook WHERE BarCode IS NOT NULL AND BarCode <> ''";
            DataSet ds = AcessHelper.ExecuteDataSet(sqlText);
            DataTable dt = ds.Tables[0];
            return dt.Rows.Count;
        }

        public DataTable GetBookToDataTableByPaging(int pageIndex, int pageSize)
        {
            int flag = (pageSize * (pageIndex - 1));

            StringBuilder sqlStr = new StringBuilder();
            if (pageIndex == 1)
            {
                sqlStr.AppendFormat(@" SELECT TOP {0} ID,BName,BarCode,Writer,Press,PublicationDate,DoorNUm FROM LocalBook WHERE BName IS NOT NULL AND BName <> '' ORDER BY ID", pageSize);
            }
            else
            {
                sqlStr.AppendFormat(@" SELECT TOP {0} ID,", pageSize);
                sqlStr.AppendFormat(@"                BName,");
                sqlStr.AppendFormat(@"                BarCode,");
                sqlStr.AppendFormat(@"                Writer,");
                sqlStr.AppendFormat(@"                Press,");
                sqlStr.AppendFormat(@"                PublicationDate,");
                sqlStr.AppendFormat(@"                DoorNUM");
                sqlStr.AppendFormat(@" FROM LocalBook");
                sqlStr.AppendFormat(@" WHERE ID NOT IN (");
                sqlStr.AppendFormat(@"                  SELECT TOP {0} ID", flag);
                sqlStr.AppendFormat(@"                         FROM LocalBook");
                sqlStr.AppendFormat(@"                  WHERE BName IS NOT NULL");
                sqlStr.AppendFormat(@"                  AND BName <> ''");
                sqlStr.AppendFormat(@"                  ORDER BY ID");
                sqlStr.AppendFormat(@"                  )");
                sqlStr.AppendFormat(@" AND BName IS NOT NULL");
                sqlStr.AppendFormat(@" AND BName <> ''");
                sqlStr.AppendFormat(@" ORDER BY ID ");
            }
            DataSet ds = AcessHelper.ExecuteDataSet(sqlStr.ToString());
            DataTable dt = ds.Tables[0];
            return dt;
        }
    }
}
